﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class KhaiSinhDAL
    {
        public static void InsertKhaiSinh(KhaiSinhPublic _ks)
        {
            KhaiSinh ks = new KhaiSinh();
            ks.MaKS = _ks.MaKS;
            ks.SoKhaiSinh = _ks.SoKhaiSinh;
            ks.NguoiDangKi = _ks.NguoiDangki;
            ks.MaQH = _ks.MaQH;
            ks.NguoiCap = _ks.NguoiCap;
            ks.ChucVu = _ks.ChucVu;
            ks.NgayCap = _ks.NgayCap;
            ks.NoiCap = _ks.NoiCap;
            ks.NoiSinh = _ks.NoiSinh;
            ks.MaNKC = _ks.MaNKC;
            ks.MaNKM = _ks.MaNKM;

            ks.CanBoHoTich = _ks.CanBoHoTich;
            ks.CanBoDangKi = _ks.CanBoDangKi;
            ks.ChucVuNguoiNhan = _ks.ChucVuNguoiNhan;
            ks.NgayNhap = _ks.NgayNhap;
            ks.NguoiNhan = _ks.NguoiNhan;
            Common.db.AddToKhaiSinhs(ks);
            Common.db.SaveChanges();
        }
        public static KhaiSinhPublic LoadKhaiSinhByMaKS(int MaKS)
        {
            var ks = Common.db.ExecuteStoreQuery<KhaiSinhPublic>(@"select MaKS,ISNULL(SoKhaiSinh,0) as SoKhaiSinh,ISNULL(NguoiDangKi,0) as NguoiDangKi,ISNULL(MaQH,0) as MaQH,ISNULL(NguoiCap,0) as NguoiCap,ISNULL(ChucVu,0) as ChucVu,ISNULL(NgayCap,'') as NgayCap,NoiSinh,ISNULL(NoiCap,0) as NoiCap,ISNULL(MaNKC,0) as MaNKC,ISNULL(MaNKM,0) as MaNKM,ISNULL(CanBoDangKi,0) as CanBoDangKi,ISNULL(CanBoHoTich,0) as CanBoHoTich,ISNULL(NgayNhap,'') as NgayNhap,ISNULL(NguoiNhan,0) as NguoiNhan,ISNULL(ChucVuNguoiNhan,0) as ChucVuNguoiNhan from KhaiSinh where MaKS=" + MaKS);
            return ks.FirstOrDefault();
        }
        //public static KhaiSinhPublic UpdateKhaiSinh(int MaKS)
        //{
        //    var ks = Common.db.ExecuteStoreQuery<KhaiSinhPublic>(@"update GiayKhaiSinh set where MaKS=" + MaKS);

        //}
        public static int CountKhaiSinhByYear(int firstyear, int lastyear)
        {
            var ks = Common.db.ExecuteStoreQuery<int>(@"select count(MaKS) from KhaiSinh where year(NgayNhap)>=" + firstyear + "and year(NgayNhap)<=" + lastyear);
            return ks.FirstOrDefault();
        }
        public static KhaiSinhPublic LoadKhaiSinhBySoKS(int SoKS)
        {
            var ks = Common.db.ExecuteStoreQuery<KhaiSinhPublic>(@"select MaKS,SoKhaiSinh as SoKhaiSinh,NguoiDangKi,MaQH,NguoiCap,ChucVu,NgayCap,NoiSinh,NoiCap,ISNULL(MaNKC,0) as MaNKC,ISNULL(MaNKM,0) as MaNKM,CanBoDangKi,CanBoHoTich,NgayNhap,NguoiNhan,ChucVuNguoiNhan from KhaiSinh where SoKhaiSinh=" + SoKS);
            return ks.FirstOrDefault();
        }
        public static bool TestSoKhaiSinh(int SoKhaiSinh)
        {
            var ks = Common.db.ExecuteStoreQuery<KhaiSinhPublic>("select SoKhaiSinh from KhaiSinh where SoKhaiSinh=" + SoKhaiSinh);
            if (ks.Count() > 0)
                return false;
            else
                return true;
        }
        public static void UpdateKhaiSinh(int SoKhaiSinh, int NoiSinh, int MaNKC, int MaNKM, string NguoiDangKi, byte MaQH, int CanBoHoTich, int NguoiCap, DateTime NgayCap, int NguoiNhan,byte ChucVu, byte ChucVuNguoiNhan,int MaKS)
        {
            string chuoi = @"update KhaiSinh set SoKhaiSinh="+SoKhaiSinh+",NoiSinh="+NoiSinh+",MaNKC="+MaNKC+",MaNKM="+MaNKM+",NguoiDangKi=N'"+NguoiDangKi+"',MaQH="+MaQH+",NguoiCap="+NguoiCap+",NgayCap='"+NgayCap+"',NguoiNhan="+NguoiNhan+",ChucVu="+ChucVu+",ChucVuNguoiNhan="+ChucVuNguoiNhan+"where MaKS="+MaKS;
            var ks = Common.db.ExecuteStoreQuery<KhaiSinhPublic>(chuoi);
            Common.db.SaveChanges();
        }
        public static void DeleteKhaiSinh(int MaKS)
        {
            var ks=Common.db.ExecuteStoreQuery<KhaiSinhPublic>("delete from KhaiSinh where MaKS="+MaKS);
            Common.db.SaveChanges();
        }
        public static int CountKhaiSinhByDay(DateTime batdau, DateTime ketthuc)
        {
            var ks = Common.db.ExecuteStoreQuery<int>("select count(MaKS) from KhaiSinh where NgayNhap>='" + batdau + "'and NgayNhap<='" + ketthuc + "'");
            return ks.FirstOrDefault();
        }
    }
}
